<?php
/**
 * Created by PhpStorm.
 * User: 戎大富
 * Date: 2020/2/20
 * Time: 10:50
 */

namespace app\admin\controller;


use think\Db;
use think\Request;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
class School extends Base {

    /**
     * 列表展示
     * @return mixed|\think\response\Json
     */
    public function index(){
        if(request()->isAjax()){
            $map = array();
            extract(input());
            if(isset($name)&&$name!="")
            {
                $map['a.school_name|school_erp|school_erp_num'] = ['like','%'.$name.'%'];
            }
            if(isset($province)&&$province!="")
            {
                $map['a.school_sheng'] = $province;
            }

            if(isset($city)&&$city!="")
            {
                $map['a.school_shi'] = $city;
            }
            if(isset($district)&&$district!="")
            {
                $map['a.school_qu'] = $district;
            }
            if(isset($status)&&$status!="")
            {
                $map['a.school_status'] = $status;
            }
            $NowPage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $count = Db::name('school')->alias('a')
                ->where($map)
//                ->join('up_area ua','ua.district_id = a.school_sheng','left')
//                ->join('up_area ua1','ua1.district_id= a.school_shi','left')
//                ->join('up_area ua2','ua2.district_id= a.school_qu','left')
                ->count();// 获取总条数
            $lists = Db::name('school')->alias('a')
//                ->join('up_area ua','ua.district_id = a.school_sheng','left')
//                ->join('up_area ua1','ua1.district_id = a.school_shi','left')
//                ->join('up_area ua2','ua2.district_id = a.school_qu','left')
//                ->order()
                ->page($NowPage,$limits)
                ->where($map)
//                ->field('a.*,ua.district,ua1.district as district1,ua2 district as district2')
                ->field('a.*')
                ->select();
//            foreach ($lists as $key=>&$vo){
//                    if($vo['school_status']==1){
//                        $vo['school_status_name'] = '上架';
//                    }else{
//                        $vo['school_status_name'] = '下架';
//                    }
//                $sheng = Db::table('up_area')->where('district_id',$vo['school_sheng'])->value('district');
//                $shi = Db::table('up_area')->where('district_id',$vo['school_shi'])->value('district');
//                $qu = Db::table('up_area')->where('district_id',$vo['school_qu'])->value('district');
//                $vo['address'] = $sheng.$shi.$qu;
//            }
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }

        $province = Db::table('up_area')->where('level', 1)->select();
        $this->assign([
            'province' => $province,
        ]);
        return $this->fetch();
    }
    /**
     * 改展示状态（是否展示过去年份）
     * @author
     */
    public function changeSchoolShow()
    {
        extract(input());
        $res = Db::table('up_school')
            ->where('school_id',$id)
            ->update([
                'school_update_time' => date('Y-m-d H:i:s'),
                'school_history' => $num
            ]);
        if($res){
            return json(['code' => 200, 'msg' => '成功']);
        }else{
            return json(['code' => 100, 'msg' => '失败']);
        }
    }
    /**
     * 改现场调换状态（是否开启现场调换）
     * @author
     */
    public function changeSchoolChange()
    {
        extract(input());
        $res = Db::table('up_school')
            ->where('school_id',$id)
            ->update([
                'school_update_time' => date('Y-m-d H:i:s'),
                'school_change' => $num
            ]);
        if($res){
            return json(['code' => 200, 'msg' => '成功']);
        }else{
            return json(['code' => 100, 'msg' => '失败']);
        }
    }
    /**
     * 改学校状态（是否冻结学校）
     * @author
     */
    public function changeSchoolStatus()
    {
        extract(input());
        $res = Db::table('up_school')
            ->where('school_id',$id)
            ->update([
                'school_status_time' => date('Y-m-d H:i:s'),
                'school_status' => $num
            ]);
        if($res){
            return json(['code' => 200, 'msg' => '成功']);
        }else{
            return json(['code' => 100, 'msg' => '失败']);
        }
    }
    /**
     * 禁止年份和交期
     * @return mixed|\think\response\Json
     */
    public function updateSort(){
        $id = input('id');
        $field = input('field');
        $value = input('value');
        if($field == 'school_done'){
            if($value === ''){
                $value = null;
            }else{
                if((int)$value <= 0 || !$value){
                    return json(['code' => 100, 'msg' =>'单位为天，必须输入数字且不能为0！']);
                }
            }

        }
        if($field == 'school_erp_num'){
            if(!$value){
                $value = '';
            }else{
                $have = Db::table('up_school')
                    ->where('school_erp_num',$value)
                    ->field('school_id')
                    ->find();
                if($have){
                    return json(['code' => 100, 'msg' =>'已经有此编号了！']);
                }
            }

        }
        $param = [
            $field => $value
        ];
        if($field == 'school_done') $param['school_done_time'] = date('Y-m-d H:i:s');
        $res = Db::name('school')->where('school_id',$id)->update($param);
        if($res){
            return json(['code' => 200, 'msg' => '修改成功']);
        }else{
            return json(['code' => 100, 'msg' =>' 修改失败']);
        }
    }
    /**
     * 交期
     * @return mixed|\think\response\Json
     */
    public function updateDone(){
        $value = input('value');
        if((int)$value <= 0 || !$value){
            return json(['code' => 100, 'msg' =>'单位为天，必须输入数字且不能为0！']);
        }
        $id = input('id');
        $param = [
            'school_done' => (int)$value,
        ];
        $res = Db::name('school')->where('school_id',$id)->update($param);
        if($res){
            return json(['code' => 200, 'msg' => '修改成功']);
        }else{
            return json(['code' => 100, 'msg' =>' 修改失败']);
        }
    }
    //---------------------------------------------------------------------
    /**
     * 列表展示
     * @return mixed|\think\response\Json
     */
    public function tong(){
        extract(input());
        if(request()->isAjax()){
            extract(input());
            $map = array();
            $map['a.st_shool_id'] = $school_id;
            if(isset($name)&&$name!="")
            {
                $map['a.st_tong_erp_number'] = ['like','%'.$name.'%'];
            }

            $NowPage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $count = Db::name('school_tong')->alias('a')
                ->where($map)
                ->count();// 获取总条数
            $lists = Db::name('school_tong')->alias('a')
                ->order('st_time desc')
                ->page($NowPage,$limits)
                ->where($map)
//                ->field('a.*,ua.district,ua1.district as district1,ua2 district as district2')
                ->field('a.*')
                ->select();
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }

        $this->assign([
            'school_id' => $school_id,
        ]);
        return $this->fetch();
    }
    //-----------------------------------学号-start----------------------------------------------
    /**
     * 学号数据
     * @return mixed|\think\response\Json
     */
    public function setnum(){
        extract(input());
        if(request()->isAjax()){
            extract(input());
            $map = array();
            if(isset($name)&&$name!="")
            {
                $map['s.sn_number'] = ['like','%'.$name.'%'];
            }
            if(isset($status)&&$status!="")
            {
                $map['s.sn_status'] = $status;
            }
            if(isset($schools)&&$schools!="")
            {
                $map['school_id'] = $schools;
            }

            $NowPage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $count = Db::name('school_number')->alias('s')
                ->join('up_school sch','s.sn_school_id=sch.school_id','LEFT')
                ->join('up_u_user_son us','s.sn_uson_id=us.uson_id','LEFT')
                ->join('up_u_user u','us.uson_user_id=u.user_id','LEFT')
                ->count();// 获取总条数
            $lists = Db::name('school_number')->alias('s')
                ->join('up_school sch','s.sn_school_id=sch.school_id','LEFT')
                ->join('up_u_user_son us','s.sn_uson_id=us.uson_id','LEFT')
                ->join('up_u_user u','us.uson_user_id=u.user_id','LEFT')
                ->order('sn_time desc')
                ->page($NowPage,$limits)
                ->where($map)
                ->field('s.*,school_id,school_name,school_erp,uson_username,user_username,user_phone')
                ->select();
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        $schools = Db::table('up_school')->field('school_id,school_name,school_erp')->select();
        $this->assign([
            'schools' => $schools,
        ]);
        return $this->fetch();
    }
    /**
     * 编辑学号
     * @return mixed|\think\response\Json
     */
    public function updateNum(){
        $id = input('id');
        $field = input('field');
        $value = input('value');
        $sn_status = Db::name('school_number')->where('sn_id',$id)->value('sn_status');
        if($sn_status != 1) return json(['code' => 100, 'msg' => '只能修改未绑定的']);
        $param = [
            $field => $value,
        ];
        $res = Db::name('school_number')->where('sn_id',$id)->update($param);
        if($res){
            return json(['code' => 200, 'msg' => '修改成功']);
        }else{
            return json(['code' => 100, 'msg' =>' 修改失败']);
        }
    }
    /**
     * 删除学号
     * @return mixed|\think\response\Json
     */
    public function sndel(){
        $id = input('id');
        $sn_status = Db::name('school_number')->where('sn_id',$id)->value('sn_status');
        if($sn_status != 1) return json(['code' => 100, 'msg' => '只能删除未绑定的']);
        $res = Db::name('school_number')->where('sn_id',$id)->delete();
        if($res){
            return json(['code' => 200, 'msg' => '成功']);
        }else{
            return json(['code' => 100, 'msg' => '失败']);
        }
    }
    /**
     * 批量删除学号
     * @return mixed|\think\response\Json
     */
    public function pldels(){
        $id = input('id');
        if(!$id) return json(['code' => 100, 'msg' => '请选择数据！']);
        $sn_status = Db::name('school_number')
            ->where('sn_id','in',$id)
            ->where('sn_status',2)
            ->find();
        if($sn_status) return json(['code' => 100, 'msg' => '只能删除未绑定的']);
        $res = Db::name('school_number')->where('sn_id','in',$id)->delete();
        if($res){
            return json(['code' => 200, 'msg' => '成功']);
        }else{
            return json(['code' => 100, 'msg' => '失败']);
        }
    }
    /**
     * 添加
     * @return mixed|\think\response\Json
     */
    public function newAdd(){
        if(Request::instance()->isPost()){
            $params = input('param.');
            $sn_number = trim($params['sn_number'], ' |,');
            if(!$sn_number) return json(['code' => 100, 'msg' => '学号不能为空']);
            $have = Db::name('school_number')
                ->where('sn_school_id',$params['sn_school_id'])
                ->where('sn_number','in',$sn_number)
                ->find();
            if($have) return json(['code' => 100, 'msg' => '此学校有已存在的学号：' . $have['sn_number']]);
            $time = date('Y-m-d H:i:s');
            $sn_number = explode(',',$sn_number);
            $sn_number2 = array_unique($sn_number);
            if(count($sn_number) != count($sn_number2)) return json(['code' => 100, 'msg' => '有重复的学号，请检查学号']);
            $school_erp = Db::table('up_school')->where('school_id',$params['sn_school_id'])->value('school_erp');
            $data = [];
            foreach ($sn_number as $k => $v){
                $data[] = [
                    'sn_school_id' => $params['sn_school_id'],
                    'sn_school_erp' => $school_erp,
                    'sn_number' => $v,
                    'sn_time' => $time,
                    'sn_update' => $time,
                ];
            }
            $res = Db::table('up_school_number')->insertAll($data);
            if($res){
                return json(['code' => 200, 'msg' => '添加成功']);
            }else{
                return json(['code' => 100, 'msg' => '添加失败']);
            }
        }
        //所有用户
        $user = Db::table('up_school')
            ->field('school_id, school_name,school_erp')
            ->where('school_status', 1)
            ->select();

        $this->assign([
            'user' => $user
        ]);
        return $this->fetch('school/numadd');
    }
    /**
     * 导入Excel
     * @return mixed
     */
    public function excelIn2(){
        set_time_limit(0);
        if(request()->file('file')){
            $file = request()->file('file');
            $info  = $file->getInfo();
            $file_path = $info['tmp_name'];
            //取出文件后缀
            $file_type = explode(".",$info['name']);
            $file_type = end($file_type);
            vendor("PHPExcel.PHPExcel.IOFactory");
            if( $file_type == 'xls' ){
                $inputFileType = 'Excel5';    //这个是读 xls的
            }else{
                $inputFileType = 'Excel2007';//这个是计xlsx的
            }
            $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($file_path);
            $objWorksheet = $objPHPExcel->getActiveSheet();
            $highestRow = $objWorksheet->getHighestRow();
//            print_r($highestRow);die;
            $time = date('Y-m-d H:i:s');
            $data = [];
            for ($i=2;$i<=$highestRow;$i++) {
                $a = $objPHPExcel->getActiveSheet ()->getCell ("A" . $i)->getValue ();//学校
                $b = $objPHPExcel->getActiveSheet ()->getCell ("B" . $i)->getValue ();//学号
                if($a != '' && $b != ''){
                    $school_id = Db::table('up_school')->where('school_erp', $a)->value('school_id');
                    $inser = [
                        'sn_school_id' => $school_id,
                        'sn_school_erp' => $a,
                        'sn_number' => $b,
                        'sn_time' => $time,
                        'sn_update' => $time
                    ];
                    $have = Db::table('up_school_number')
                        ->where('sn_school_erp',$a)
                        ->where('sn_number',$b)
                        ->find();
                    if(!$have && $school_id){
                        $data[] = $inser;
                    }
                }
            }
            if($data){
                $res = Db::table('up_school_number')->insertAll($data);
                if($res){
                    echo '/admin/School/setnum';
                }else{
                    echo '/admin/School/excelin2';
                }
            }else{
                echo '/admin/School/setnum';
            }
        }
        return $this->fetch('school/excelin2');
    }

    public function excelOutNum()
    {

        set_time_limit(0);//防止超时
//        ini_set("memory_limit", "256M");//防止内存溢出
        extract(input());
        $map = array();
        if(isset($name)&&$name!="")
        {
            $map['s.sn_number'] = ['like','%'.$name.'%'];
        }
        if(isset($status)&&$status!="")
        {
            $map['s.sn_status'] = $status;
        }
        if(isset($schools)&&$schools!="")
        {
            $map['school_id'] = $schools;
        }
        $data = Db::name('school_number')->alias('s')
            ->join('up_school sch','s.sn_school_id=sch.school_id','LEFT')
            ->join('up_u_user_son us','s.sn_uson_id=us.uson_id','LEFT')
            ->join('up_u_user u','us.uson_user_id=u.user_id','LEFT')
            ->order('sn_time desc')
            ->where($map)
            ->field('sn_number,sn_status,sn_bindtime,sn_time,school_name,school_erp,uson_username,user_username,user_phone')
            ->select();
        if($data){
            $cellname = [
                ['sn_number','学号',15,'LEFT'],
                ['sn_status','绑定状态',15,'LEFT'],
                ['sn_bindtime','绑定时间',20,'LEFT'],
                ['sn_time','新建时间',20,'LEFT'],
                ['school_name','学校名字',20,'LEFT'],
                ['school_erp','学校标识',20,'LEFT'],
                ['uson_username','绑定学生名字',20,'LEFT'],
                ['user_username','所属用户',20,'LEFT'],
                ['user_phone','用户电话',20,'LEFT']
            ];
            $res = excelNewPlugin('学号数据','student',$cellname,$data);
            return json($res);
        }else{
            return json(['code'=>0,'msg'=>'此条件下无数据']);
        }
    }
    //-----------------------------------学号--end-----------------------------------------------
    //导出
    public function excelOutSchool()
    {
        set_time_limit(0);//防止超时
        // ini_set("memory_limit", "256M");//防止内存溢出
        $map = array();
        extract(input());
        if(isset($name)&&$name!="")
        {
            $map['a.school_name'] = ['like','%'.$name.'%'];
        }
        if(isset($province)&&$province!="")
        {
            $map['a.school_sheng'] = $province;
        }

        if(isset($city)&&$city!="")
        {
            $map['a.school_shi'] = $city;
        }
        if(isset($district)&&$district!="")
        {
            $map['a.school_qu'] = $district;
        }
        if(isset($status)&&$status!="")
        {
            $map['a.school_status'] = $status;
        }
        $field = ['school_name','school_address','school_erp','school_no_years', 'school_status',
            'school_head','school_shui'
        ];
        $data = Db::name('school')->alias('a')
            ->where($map)
            ->field($field)
            ->select();
        if($data){
            $cellname = [
                '学校名字','省市区', '学校标识', '禁止年份', '学校状态',
                '抬头', '税号'

            ];
            $status = ['school_status'];
            $values = [
                'school_status' => [1 => '正常',2 => '冻结']
            ];
            $res = exportExcel('schoolDatas',time(),$cellname,$data,$status,$values);
            return json($res);
        }else{
            return json(['code'=>0,'msg'=>'此条件下无数据']);
        }
    }
}